﻿--Truy van 1
create proc sp_DanhSachBenhNhan
(
	@CMND nvarchar(50)
)
as
begin
	SELECT MaBN, CMND, DienThoai, HoTen, NgaySinh, DiaChi FROM BenhNhan WHERE HoTen = @CMND
END
go

drop proc sp_DanhSachBenhNhan
--Truy van 2:
create proc sp_ThemMoiHoSoBenhNhan
(
	@CMND nvarchar(50), 
	@DienThoai nvarchar(50), 
	@HoTen nvarchar(50), 
	@NgaySinh datetime, 
	@DiaChi nvarchar(50), 
	@GioiTinh int,
	@SoThe int,
	@NgayHetHang datetime,
	@TinhTrang int
)
as
begin
	If EXISTS (SELECT HoTen FROM BenhNhan WHERE CMND = @CMND)
	begin
		raiserror('Đã tồn tại bệnh nhân này', 16, 1)
		rollback tran
	end
	insert into BenhNhan(CMND, DienThoai, HoTen,  NgaySinh, DiaChi, GioiTinh, SoThe, NgayHetHang, TinhTrang) 
	values (@CMND, @DienThoai, @HoTen,  @NgaySinh, @DiaChi, @GioiTinh, @SoThe, @NgayHetHang, 1)		
END
go

create proc sp_DanhSachBenhNhan_CMND
(
	@CMND nvarchar(50)
)
as
begin
	SELECT MaBN, CMND, DienThoai, HoTen, NgaySinh, DiaChi FROM BenhNhan WHERE CMND = @CMND
END
go

drop proc sp_DanhSachBenhNhan_CMND

--Truy Vấn 3
create proc sp_ThemMoiHoSoBenhAn
(
	@XetNghiem nvarchar(50), 
	@ChuanDoan nvarchar(50), 
	@ChiDinh nvarchar(50),  
	@MaBN int, 
	@HoTen nvarchar(50),
	@CMND nvarchar(50),
	@TinhTrangBenh nvarchar(50),
	@SoPhong int,
	@NgayKham datetime
)
as
begin
	begin tran
		declare @_MaBN int
		declare @KetQua nvarchar(50)
		declare @_MaBA int
		if exists(select MaBN from BenhNhan where HoTen=@hoten and CMND = @CMND)
		begin 
			--lay thong tin benh nhan
			set @_MaBN = (select MaBN from BenhNhan where HoTen=@hoten and CMND = @CMND)
			----them benh can cho benh nhan
			insert into BenhAn(XetNghiem, ChuanDoan, ChiDinh,  MaBN, TinhTrang) 
			values (@XetNghiem, @ChuanDoan, @ChiDinh,  @_MaBN, 1)
			--Luu thong tin benh an vao ho so nhap vien
			set @_MaBA = (select MaBA from BenhNhan bn, BenhAn ba where bn.MaBN = ba.MaBN and bn.HoTen = @HoTen and bn.CMND = @CMND)
			insert into HoSoNhapVien(TinhTrangBenh, SoPhong, NgayKham, MaBA, TinhTrang)
			values (@TinhTrangBenh, @SoPhong, @NgayKham, @_MaBA, 1)
		end
		if @@ERROR = 0
		set @KetQua =N'Thêm hồ sơ bệnh án thành công!'
		else
		begin
			raiserror('Có lỗi xảy ra', 16, 1)
			rollback
			return
		end
	commit
END
go
--truy vấn 4: Dựa vào mã hồ sơ bệnh nhân cho biết tiểu sử bệnh và các dịch vụ (xét nghiệm, siêu âm) 
--mà bệnh nhân đã thực hiện trong 6 tháng gần đây nhất.
create proc sp_TieuSuBenh  
@MaHS int
as
begin
	if not exists (select MaHS from HoSoNhapVien where MaHS=@MaHS)
	return
	else
	begin
		--lấy tiểu sử bệnh trong vòng 6 tháng gần đây
		select nk.STT, nk.TinhTrangBenh, nk.NgayKham 
		from NhatKy nk, HoSoNhapVien hs, LanKham lk
		where(hs.MaHS = nk.MaHS or nk.MaHS = lk.MaHS) and hs.MaHS = @MaHS and DATEDIFF(MM, hs.NgayKham, GETDATE())<6
		--Lấy lịch sử các dịch vụ
		select dv.MaDV, dv.TenDichVu, hddv.NgayLap 
		from HoaDonDichVu hddv, ChiTietHoaDonDichVu cthd, DichVu dv, HoSoNhapVien hs
		where hs.MaHS = @MaHS and hs.MaHS = hddv.MaHS and hddv.SoHDDV = cthd.SoHDDV and cthd.MaDV = dv.MaDV 
		and DATEDIFF(MM, hs.NgayKham, GETDATE())<6
		group by dv.MaDV, dv.TenDichVu, hddv.NgayLap 
	end
END
go
--Truy vấn 5: Thêm mới hồ sơ nhập viện
create proc sp_ThemMoiHoSoNhapVien
(
	@CMND nvarchar(50), 
	@DienThoai nvarchar(50), 
	@HoTen nvarchar(50), 
	@NgaySinh datetime, 
	@DiaChi nvarchar(50), 
	@GioiTinh int,
	@SoPhong int,
	@MaBS int,
	@SoThe int,
	@NgayHetHang datetime,
	@XetNghiem nvarchar(50),
	@ChuanDoan nvarchar(50),
	@ChiDinh nvarchar (50),
	@TinhTrangBenh nvarchar(100),
	@TinhTrang int
)
as
begin
	begin tran
	declare @KetQua nvarchar(100)
	--Kiểm tra xem hồ sơ bệnh nhân có tồn tại hay chưa?
	if exists(select hs.MaHS from HoSoNhapVien hs, BenhAn ba, BenhNhan bn where hs.MaBA = ba.MaBA and ba.MaBN = bn.MaBN and bn.CMND = @CMND  )
		return 0
	else
	begin
		--Them mới thông tin bệnh nhân
		If EXISTS (SELECT HoTen FROM BenhNhan WHERE CMND = @CMND)
		begin
			raiserror('Đã tồn tại bệnh nhân này', 16, 1)
			rollback tran
		end
		insert into BenhNhan(CMND, DienThoai, HoTen,  NgaySinh, DiaChi, GioiTinh, SoThe, NgayHetHang, TinhTrang) 
		values (@CMND, @DienThoai, @HoTen,  @NgaySinh, @DiaChi, @GioiTinh, @SoThe, @NgayHetHang, 1)		
		--Lấy thông tin bệnh nhân
		declare @_MaBN int
		set @_MaBN = (select bn.MaBN from BenhNhan bn where bn.CMND = @CMND)
		--Thêm bệnh án bệnh nhân
		insert into BenhAn (XetNghiem, ChuanDoan, ChiDinh, MaBA, TinhTrang)
		values (@XetNghiem, @ChuanDoan, @ChiDinh, @_MaBN, 1)
		--Lấy thông tin bệnh án
		declare @_MaBA int
		set @_MaBA = (select ba.MaBA from BenhAn ba, BenhNhan bn where bn.CMND = @CMND)
		---Thêm hồ sơ nhập viện
		insert into HoSoNhapVien(TinhTrangBenh, SoPhong, NgayKham, MaBA, TinhTrang)
		values(@TinhTrangBenh, @SoPhong, GETDATE(), @_MaBA, 1)
		--Lấy thông tin Hồ sơ nhập viện
		declare @_MaHS int
		set @_MaHS = (select hsnv.MaHS from HoSoNhapVien hsnv, BenhAn ba, BenhNhan bn 
						where hsnv.NgayKham = GETDATE() and hsnv.MaBA = ba.MaBA and ba.MaBN = bn.MaBN and bn.CMND =@CMND)
		--Thêm nhật ký tình trạng ban đầu của bệnh và bác sĩ phụ trách.
		insert into NhatKy(TinhTrangBenh, ChuKy, NgayKham, MaBS, MaHS)
		values (@TinhTrangBenh, '', GETDATE(), @MaBS, @_MaHS)
	end
	if @@ERROR = 0
	set @KetQua =N'Thêm hồ sơ nhập viện thành công!'
	else
	begin
		raiserror('Có lỗi xảy ra', 16, 1)
		rollback
		return
	end
	commit
END
go

--Truy vấn 6: Thêm mới hóa đơn xuất viện.
create proc sp_ThemMoiHoaDonXuatVien
(
	@MaHS int
)
as
begin
	begin tran
	declare @KetQua nvarchar(100)
	declare @_TongTienPhong float
	declare @_TongTienDichVu float
	declare @_TongTienThuoc float
	--Kiểm tra xem hồ sơ bệnh nhân có tồn tại hay chưa?
	if not exists(select hs.MaHS from HoSoNhapVien hs)
		return 0
	else
	begin
		--tính tổng tiền phòng
		
		set @_TongTienPhong = (select SUM(cthdp.ThanhTien) from ChiTietHoaDonPhong cthdp, Phong p, HoSoNhapVien hs, HoaDonPhong hdp 
								where hs.MaHS = hdp.MaHS and hdp.SoHDPH = cthdp.SoHDPH and cthdp.SoPhong = p.SoPhong and hs.MaHS = @MaHS
								group by hs.MaHS) 
	    --tính tông tiền dịch vụ  
	    set @_TongTienDichVu = (select SUM(cthddv.ThanhTien) 
								from HoSoNhapVien hs, HoaDonDichVu hddv, ChiTietHoaDonDichVu cthddv, DichVu dv
								where hs.MaHS = hddv.MaHS and hddv.SoHDDV = cthddv.SoHDDV and cthddv.MaDV = dv.MaDV and hs.MaHS = @MaHS
								group by hs.MaHS)
		--tinh tong tiền thuốc
		set @_TongTienThuoc = (select SUM(cthdt.ThanhTien) 
								from HoaDonThuoc hdt, ChiTietHoaDonThuoc cthdt, Thuoc t, HoSoNhapVien hs
								where hs.MaHS = hdt.MaHS and hdt.SoHDTH = cthdt.SoHDTH and cthdt.TenThuoc = t.TenThuoc and cthdt.KhoiLuong = t.KhoiLuong
								group by hs.MaHS)
	-- thanh toán các hóa đơn (xuất tổng tiền và update tình trạng thanh toán các hóa đơn)
	update HoaDonDichVu set TinhTrang = 0 where HoaDonDichVu.MaHS = @MaHS --tinh trạng 1 là chưa thanh toán, 0 là đã thanh toán
	update HoaDonPhong set TinhTrang = 0 where HoaDonPhong.MaHS = @MaHS
	update HoaDonThuoc set TinhTrang = 0 where HoaDonThuoc.MaHS = @MaHS
	end
	if @@ERROR = 0
	set @KetQua =N'Thanh toán hồ sơ xuất viện thành công!'
	else
	begin
		raiserror('Có lỗi xảy ra', 16, 1)
		rollback
		return
	end
	commit
END
go
--truy vấn 7: thêm mới hóa đơn tạm ứng
create proc sp_ThemMoiHoaDonTamUng
(
	@XetNghiem nvarchar(50), 
	@ChuanDoan nvarchar(50), 
	@ChiDinh nvarchar(50),  
	@MaBN int, 
	@HoTen nvarchar(50),
	@CMND nvarchar(50),
	@TinhTrangBenh nvarchar(50),
	@SoPhong int,
	@NgayKham datetime,
	@MaTN int
)
as
begin
	begin tran
		declare @_MaBN int
		declare @KetQua nvarchar(50)
		declare @_MaBA int
		declare @_TongTienPhong float
		if exists(select MaBN from BenhNhan where HoTen=@hoten and CMND = @CMND)
		begin 
			--lay thong tin benh nhan
			set @_MaBN = (select MaBN from BenhNhan where HoTen=@hoten and CMND = @CMND)
			----them benh can cho benh nhan
			insert into BenhAn(XetNghiem, ChuanDoan, ChiDinh,  MaBN, TinhTrang) 
			values (@XetNghiem, @ChuanDoan, @ChiDinh,  @_MaBN, 1)
			--Luu thong tin benh an vao ho so nhap vien
			set @_MaBA = (select MaBA from BenhNhan bn, BenhAn ba where bn.MaBN = ba.MaBN and bn.HoTen = @HoTen and bn.CMND = @CMND)
			insert into HoSoNhapVien(TinhTrangBenh, SoPhong, NgayKham, MaBA, TinhTrang)
			values (@TinhTrangBenh, @SoPhong, @NgayKham, @_MaBA, 1)
			--Lay thông tin hồ sơ nhập viên
			declare @_MaHS int
			set @_MaHS = (select hsnv.MaHS from HoSoNhapVien hsnv, BenhAn ba, BenhNhan bn 
						where hsnv.NgayKham = GETDATE() and hsnv.MaBA = ba.MaBA and ba.MaBN = bn.MaBN and bn.CMND =@CMND)
			set @_TongTienPhong = (select SUM(cthdp.ThanhTien) from ChiTietHoaDonPhong cthdp, Phong p, HoSoNhapVien hs, HoaDonPhong hdp 
						where hs.MaHS = hdp.MaHS and hdp.SoHDPH = cthdp.SoHDPH and cthdp.SoPhong = p.SoPhong and hs.MaHS = @_MaHS
						group by hs.MaHS) 
			
			--Thanh toán hóa đơn phòng tạm ứng
			update HoaDonPhong set TinhTrang = 0 where HoaDonPhong.MaHS = @_MaHS
		end
		if @@ERROR = 0
		set @KetQua =N'Thêm hóa đơn tạm ứng thành công thành công!'
		else
		begin
			raiserror('Có lỗi xảy ra', 16, 1)
			rollback
			return
		end
	commit
end